<?
/***************************************************************************
*                            Dolphin Smart Community Builder
*                              -------------------
*     begin                : Mon Mar 23 2006
*     copyright            : (C) 2007 BoonEx School
*     website              : http://www.boonex.com
* This file is part of Dolphin - Smart Community Builder
*
* Dolphin is free software; you can redistribute it and/or modify it under
* the terms of the GNU General Public License as published by the
* Free Software Foundation; either version 2 of the
* License, or  any later version.
*
* Dolphin is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU General Public License for more details.
* You should have received a copy of the GNU General Public License along with Dolphin,
* see license.txt file; if not, write to marketing@boonex.com
***************************************************************************/

bx_import('BxDolTwigModuleDb');

/*
 * Schools module Data
 */
class BxSchoolsDb extends BxDolTwigModuleDb {	

	/*
	 * Constructor.
	 */
	function BxSchoolsDb(&$oConfig) {
        parent::BxDolTwigModuleDb($oConfig);

		//[begin] - ultimate schools mod from modzzz 
		$this->_oConfig = $oConfig;
		//[end] - ultimate schools mod from modzzz 
 
        $this->_sTableMain = 'main';
        $this->_sTableMediaPrefix = '';
        $this->_sFieldId = 'id';
        $this->_sFieldAuthorId = 'author_id';
        $this->_sFieldUri = 'uri';
        $this->_sFieldTitle = 'title';
        $this->_sFieldDescription = 'desc';
        $this->_sFieldTags = 'tags';
        $this->_sFieldThumb = 'thumb';
        $this->_sFieldStatus = 'status';
        $this->_sFieldFeatured = 'featured';
        $this->_sFieldCreated = 'created';
        $this->_sFieldJoinConfirmation = 'join_confirmation';
        $this->_sFieldFansCount = 'fans_count';
        $this->_sFieldStudentCount = 'students_count';
        $this->_sFieldAlumniCount = 'alumni_count';

        $this->_sTableFans = 'fans';
        $this->_sTableAdmins = 'admins';
        $this->_sFieldAllowViewTo = 'allow_view_school_to';
	}

    function deleteEntryByIdAndOwner ($iId, $iOwner, $isAdmin) {
        if ($iRet = parent::deleteEntryByIdAndOwner ($iId, $iOwner, $isAdmin)) {
            $this->query ("DELETE FROM `" . $this->_sPrefix . "fans` WHERE `id_entry` = $iId");
            $this->query ("DELETE FROM `" . $this->_sPrefix . "admins` WHERE `id_entry` = $iId");
            $this->query ("DELETE FROM `" . $this->_sPrefix . "activity` WHERE `school_id` = $iId");
			$this->deleteEntryMediaAll ($iId, 'images');
            $this->deleteEntryMediaAll ($iId, 'videos');
            $this->deleteEntryMediaAll ($iId, 'sounds');
            $this->deleteEntryMediaAll ($iId, 'files');
        }
        return $iRet;
    }

    function addFan ($iEntryId, $iProfileId, $sYearEntered, $sYearLeft='', $sMembership='') {
		$iTime = time();

		$iRet = $this->query ("INSERT INTO `" . $this->_sPrefix . "fans` SET `id_entry`=$iEntryId, `id_profile` = $iProfileId, `membership_type`='$sMembership', `year_entered`='$sYearEntered', `year_left`='$sYearLeft', `when`=$iTime, `confirmed`=1"); 
	
        if ($iRet){ 
			$sField = ($sMembership=='student') ? $this->_sFieldStudentCount : $this->_sFieldAlumniCount ;
 
            $this->query ("UPDATE `" . $this->_sPrefix . "main` SET `" . $sField . "` = `" . $sField . "` + 1 WHERE `id` = '$iEntryId'");
			
			$this->flagActivity('join', $iEntryId, $iProfileId);  
		}

		return $iRet; 
	}
  
 	function getStateName($sState){
		$sState = $this->getOne("SELECT `State` FROM `States` WHERE `StateCode`='{$sState}' LIMIT 1");
		
		return $sState;
	}

	function getStateOptions($sCountry='') {
		$aStates = $this->getStateArray ($sCountry);

		foreach($aStates as $aEachCode=>$aEachState){ 
			$sOptions .= "<option value='{$aEachCode}'>{$aEachState}</option>";
		}

		return $sOptions;
	}

 	function getStateArray($sCountry=''){
 
		$aStates = array();
		$aDbStates = $this->getAll("SELECT * FROM `States` WHERE `CountryCode`='{$sCountry}'  ORDER BY `State` ");
		 
		foreach ($aDbStates as $aEachState){
			$sState = $aEachState['State'];
			$sStateCode = $aEachState['StateCode'];
			
			$aStates[$sStateCode] = $sState;
  		} 
		return $aStates;
	}
 
	function getCategories($sType)
	{ 
 		$aAllEntries = $this->getAll("SELECT `Category` FROM `sys_categories` WHERE `Type` = '{$sType}' AND `Status`='active' AND Owner=0"); 
		
		return $aAllEntries; 
	}

	function getCategoryCount($sType,$sCategory)
	{ 
		$iNumCategory = $this->getOne("SELECT count(`" . $this->_sPrefix . "main`.`id`) FROM `" . $this->_sPrefix . "main`  inner JOIN `sys_categories` ON `sys_categories`.`ID`=`" . $this->_sPrefix . "main`.`id` WHERE 1 AND  `sys_categories`.`Category` IN('{$sCategory}') AND `sys_categories`.`Type` = '{$sType}' AND `" . $this->_sPrefix . "main`.`status`='approved'"); 
		
		return $iNumCategory;
	}

	function flagActivity($sType, $iEntryId, $iProfileId, $aParams=array()){
 
		if(!$iEntryId)
			return;

		switch($sType){ 
			case 'mark_as_featured':
				foreach($aParams as $sKey=>$iValue){ 
					if($sKey=='Featured'){
						if($iValue)
							$sType = "unfeatured";
						else
							$sType = "featured"; 
					}
				}
			break; 
		}

		$aTypes = array(
			'add' => '_modzzz_schools_feed_post',
			'delete' => '_modzzz_schools_feed_delete',
			'change' => '_modzzz_schools_feed_post_change',
			'join' => '_modzzz_schools_feed_join',
			'unjoin' => '_modzzz_schools_feed_unjoin',
			'remove' => '_modzzz_schools_feed_remove',
			'rate' => '_modzzz_schools_feed_rate',
			'commentPost' => '_modzzz_schools_feed_comment',
			'featured' => '_modzzz_schools_feed_featured',
			'unfeatured' => '_modzzz_schools_feed_unfeatured',
			'makeAdmin' => '_modzzz_schools_feed_make_admin',
			'removeAdmin' => '_modzzz_schools_feed_remove_admin'  
		);
   
		$aDataEntry = $this->getEntryById($iEntryId);
		
		$sProfileNick = getNickName($iProfileId);
		$sProfileLink = getProfileLink($iProfileId);
		$sSchoolUri = $aDataEntry['uri'];
		$sSchoolTitle = process_db_input($aDataEntry['title']);
		$sSchoolUrl = BX_DOL_URL_ROOT . $this->_oConfig->getBaseUri() . 'view/' . $sSchoolUri;
	
		$sParams = "profile_id|{$iProfileId};profile_link|{$sProfileLink};profile_nick|{$sProfileNick};entry_url|{$sSchoolUrl};entry_title|{$sSchoolTitle}";

		$sLangKey = $aTypes[$sType];
 
		$this->query("INSERT INTO modzzz_schools_activity(`school_id`,`lang_key`,`params`,`type`) VALUES ($iEntryId,'$sLangKey','$sParams','$sType')");

	}

	function getActivityFeed($iLimit=5){

		return $this->getAll("SELECT `school_id`,`lang_key`,`params`,`type`,UNIX_TIMESTAMP(`date`) AS `date` FROM `modzzz_schools_activity` ORDER BY `date` DESC LIMIT $iLimit");

	}

	function getLatestComments($iLimit=5){

		return $this->getAll("SELECT `cmt_object_id`,`cmt_author_id`,`cmt_text`, UNIX_TIMESTAMP(`cmt_time`) AS `date` FROM `modzzz_schools_cmts` WHERE `cmt_text` NOT LIKE '<object%' ORDER BY `cmt_time` DESC LIMIT $iLimit");

	}  
 
	function getMySchools($iProfileId){
        $aSchools = $this->getAll ("SELECT DISTINCT m.`id` FROM `" . $this->_sPrefix . "main` m, `" . $this->_sPrefix . "fans` f WHERE m.`id`=f.`id_entry` AND  f.`id_profile` = '$iProfileId' AND  `membership_type` IN ('student','alumni') AND f.`confirmed`=1");
		 
		$aList = array();
		$aList[0] = -1; 
		foreach($aSchools as $aEachSchool){
			$aList[] = $aEachSchool['id']; 
		}
 
		return $aList; 
	}

    function getAlumniBrowse(&$aProfiles, $iEntryId, $iStart, $iMaxNum) {
        return $this->getAlumni($aProfiles, $iEntryId, true, $iStart, $iMaxNum);
    }

    function getStudentsBrowse(&$aProfiles, $iEntryId, $iStart, $iMaxNum) {
        return $this->getStudents($aProfiles, $iEntryId, true, $iStart, $iMaxNum);
    }

    function getAllFans(&$aProfiles, $iEntryId, $isConfirmed, $iStart, $iMaxNum, $aFilter = array()) {
        $isConfirmed = $isConfirmed ? 1 : 0;
        $sFilter = '';
        if ($aFilter) {
            $s = implode (' OR `f`.`id_profile` = ', $aFilter);
            $sFilter = ' AND (`f`.`id_profile` = ' . $s . ') ';
        }
        $aProfiles = $this->getAll ("SELECT SQL_CALC_FOUND_ROWS `p`.* FROM `Profiles` AS `p` INNER JOIN `" . $this->_sPrefix . $this->_sTableFans . "` AS `f` ON (`f`.`id_entry` = '$iEntryId' AND `f`.`id_profile` = `p`.`ID` AND `confirmed` = '$isConfirmed' AND `p`.`Status` = 'Active' $sFilter) ORDER BY `f`.`when` DESC LIMIT $iStart, $iMaxNum");
        return $this->getOne("SELECT FOUND_ROWS()");
    }

    function getFans(&$aProfiles, $iEntryId, $isConfirmed, $iStart, $iMaxNum, $aFilter = array()) {
        $isConfirmed = $isConfirmed ? 1 : 0;
        $sFilter = '';
        if ($aFilter) {
            $s = implode (' OR `f`.`id_profile` = ', $aFilter);
            $sFilter = ' AND (`f`.`id_profile` = ' . $s . ') ';
        }
        $aProfiles = $this->getAll ("SELECT SQL_CALC_FOUND_ROWS `p`.* FROM `Profiles` AS `p` INNER JOIN `" . $this->_sPrefix . $this->_sTableFans . "` AS `f` ON (`f`.`id_entry` = '$iEntryId' AND `f`.`id_profile` = `p`.`ID` AND `confirmed` = '$isConfirmed' AND `f`.`membership_type` = 'fan' AND `p`.`Status` = 'Active' $sFilter) ORDER BY `f`.`when` DESC LIMIT $iStart, $iMaxNum");
        return $this->getOne("SELECT FOUND_ROWS()");
    }

    function getStudents(&$aProfiles, $iEntryId, $isConfirmed, $iStart, $iMaxNum, $aFilter = array()) {
        $isConfirmed = $isConfirmed ? 1 : 0;
        $sFilter = '';
        if ($aFilter) {
            $s = implode (' OR `f`.`id_profile` = ', $aFilter);
            $sFilter = ' AND (`f`.`id_profile` = ' . $s . ') ';
        }
        $aProfiles = $this->getAll ("SELECT SQL_CALC_FOUND_ROWS `p`.* FROM `Profiles` AS `p` INNER JOIN `" . $this->_sPrefix . $this->_sTableFans . "` AS `f` ON (`f`.`id_entry` = '$iEntryId' AND `f`.`id_profile` = `p`.`ID` AND `f`.`membership_type` = 'student' AND `p`.`Status` = 'Active' $sFilter) ORDER BY `f`.`when` DESC LIMIT $iStart, $iMaxNum");
        return $this->getOne("SELECT FOUND_ROWS()");
    }

    function getAlumni(&$aProfiles, $iEntryId, $isConfirmed, $iStart, $iMaxNum, $aFilter = array()) {
        $isConfirmed = $isConfirmed ? 1 : 0;
        $sFilter = '';
        if ($aFilter) {
            $s = implode (' OR `f`.`id_profile` = ', $aFilter);
            $sFilter = ' AND (`f`.`id_profile` = ' . $s . ') ';
        }
        $aProfiles = $this->getAll ("SELECT SQL_CALC_FOUND_ROWS `p`.* FROM `Profiles` AS `p` INNER JOIN `" . $this->_sPrefix . $this->_sTableFans . "` AS `f` ON (`f`.`id_entry` = '$iEntryId' AND `f`.`id_profile` = `p`.`ID` AND `f`.`membership_type` = 'alumni' AND `p`.`Status` = 'Active' $sFilter) ORDER BY `f`.`when` DESC LIMIT $iStart, $iMaxNum");
        return $this->getOne("SELECT FOUND_ROWS()");
    }
  
    function isFan($iEntryId, $iProfileId, $isConfirmed) {
        $isConfirmed = $isConfirmed ? 1 : 0;
        return $this->getOne ("SELECT `when` FROM `" . $this->_sPrefix . $this->_sTableFans . "` WHERE `id_entry` = '$iEntryId' AND `id_profile` = '$iProfileId' AND `confirmed` = '$isConfirmed' LIMIT 1");
    }

    function isStudent($iEntryId, $iProfileId, $isConfirmed) { 
        return $this->getOne ("SELECT `when` FROM `" . $this->_sPrefix . $this->_sTableFans . "` WHERE `id_entry` = '$iEntryId' AND `id_profile` = '$iProfileId' AND `membership_type` = 'student' LIMIT 1");
    }
 
    function isAlumni($iEntryId, $iProfileId, $isConfirmed) { 
        return $this->getOne ("SELECT `when` FROM `" . $this->_sPrefix . $this->_sTableFans . "` WHERE `id_entry` = '$iEntryId' AND `id_profile` = '$iProfileId' AND `membership_type` = 'alumni'  LIMIT 1");
    }


}

?>
